

import psycopg2
import psycopg2.pool
import psycopg2.extras

from setting import *

import logging

logging.basicConfig(level = logging.INFO,format = '%(asctime)s - %(name)s - %(levelname)s - %(message)s')

logger = logging.getLogger(__name__)

class PostgerSingleton:
    _instance = None


    def __new__(cls, *args, **kwargs):

        if not cls._instance:

            cls._instance = super().__new__(cls)

        return cls._instance

    def __enter__(self):

        self.conn = self.db_pool.getconn()  # 从连接池获取连接

        self.conn.set_session(autocommit=True)  # 设置自动提交事务

        self.cur = self.conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)

        return self.conn, self.cur

    def __exit__(self, exc_type, exc_val, exc_tb):

        if exc_type is not None:

            logger.error('Exception occurred: %s', exc_val)

        self.cur.close()  # 关闭游标

        self.db_pool.putconn(self.conn)  # 将连接归还到连接池
    
    def __init__(self) -> None:
        
        # 创建一个进程池
        self.db_pool = psycopg2.pool.SimpleConnectionPool(
            minconn=1,
            maxconn=100,
            host=DATABASE_HOST,
            port=DATABASE_PORT,
            database=DATABASE_NAME,
            user=DATABASE_USER,
            password=DATABASE_PSWD
        )

    

if __name__ == "__main__":

    #调用示例

    with PostgerSingleton() as pg_singleton:
        conn, cur = pg_singleton  # 获取数据库连接和游标对象

        cur.execute("select * from cabinet where mac=%s", ("620790426a0e",))  # 执行 SQL 查询
        rows = cur.fetchone()  # 检索查询结果

        logger.info(rows)